package com.wanxi.dao.Impl;

import com.wanxi.dao.UserDao;
import com.wanxi.model.UserModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Li
 * @Date 2023/5/6 14:41
 */
public class UserDaoImpl implements UserDao {

    /**
     * 添加数据
     *
     * @param model
     * @return
     */
    public int add(UserModel model) {
        String sql = "INSERT INTO `user` (`username`, `password`, `age`," +
                " `sex`, `phone`, `email`, `address`, " +
                "`create_time`, `update_time`, `enable`, `remark`) " +
                "VALUES ('" + model.getUsername() + "', '" + model.getPassword() + "', '" + model.getAge() + "', " +
                "'" + model.getSex() + "', '" + model.getPhone() + "', '" + model.getEmail() + "', " +
                "'" + model.getAddress() + "', now(), sysdate(), '"+model.getEnable()+"', '"+model.getRemark()+"');";
        return JDBC.update(sql);
    }

    /**
     * 删除数据
     *
     * @param model
     * @return
     */
    public int delete(UserModel model) {
        String sql = "DELETE FROM `user` WHERE id=" + model.getId();
        return JDBC.update(sql);
    }

    /**
     * 修改数据
     *
     * @param model
     * @return
     */
    public int update(UserModel model) {
        String sql = "UPDATE `user` SET `username` = '" + model.getUsername() + "', " +
                "`password` = '" + model.getPassword() + "'," +
                " `age` = '" + model.getAge() + "', `sex` = '" + model.getSex() + "'," +
                " `phone` = '" + model.getPhone() + "', `email` = '" + model.getEmail() + "'," +
                " `address` = '" + model.getAddress() + "', `update_time` = now()," +
                " `enable` = '" + model.getEnable() + "', `remark` = '" + model.getRemark() + "' WHERE (`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    public boolean findSameName(UserModel model) {
        String sql = "select * from user where username='" + model.getUsername() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 判断电话号是否相同
     * @param model
     * @return
     */
    @Override
    public boolean findSamePhone(UserModel model) {
        String sql = "select * from user where phone='" + model.getPhone() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 判断邮箱是否相同
     * @param model
     * @return
     */
    @Override
    public boolean findSameEmail(UserModel model) {
        String sql = "select * from user where email='" + model.getEmail() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 登录查询用户名和密码
     * @param userModel
     * @return
     */
    @Override
    public boolean findNameAndPass(UserModel userModel) {
        String sql = "select * from `user` where username = '"+userModel.getUsername()+"' and password = '"+userModel.getPassword()+"' ";
       ResultSet query = JDBC.query(sql);
       try{
           while (query.next()){
               return true;
           }
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
        return false;
    }

    /**
     * 修改密码
     * @param userModel
     * @return
     */
    @Override
    public Integer updatePassword(UserModel userModel) {
        String sql = " update `user` set  password = '"+userModel.getPassword()+"' where id = '"+userModel.getId()+"'";
        return JDBC.update(sql);
    }

    /**
     * 重置密码
     * @param userModel
     * @return
     */
    @Override
    public Integer resetPassword(UserModel userModel) {
        String sql = "  update `user` set  password = '4QrcOUm6Wau+VuBX8g+IPg==' where id = '"+userModel.getId()+"'";
        return JDBC.update(sql);
    }

    /**
     * 查询总数
     * @return
     */
    @Override
    public Integer totalCount() {
        String sql = "select count(*) as count from `user`";
        return JDBC.count(sql);
    }


    public List<UserModel> findAll(UserModel model) {
        int start = (model.getPage()-1)*model.getLimit();
        String sql = " select * from user where true ";
        sql += getWhereSql(model);
        sql += " order by id desc";
        sql += " limit "+start+","+model.getLimit()+"";
        ResultSet query = JDBC.query(sql);
        List<UserModel> list = new ArrayList<UserModel>();
        try {
            while (query.next()) {
                list.add(UserModel.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .sex(query.getString("sex"))
                        .age(query.getInt("age"))
                        .phone(query.getString("phone"))
                        .address(query.getString("address"))
                        .email(query.getString("email"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    private String getWhereSql(UserModel model){
        String sql = "";
        if (!"".equals(model.getUsername())) {
            sql += " and username like '%" + model.getUsername() + "%' ";
        }
        if (!"".equals(model.getSex())) {
            sql += " and sex='" + model.getSex() + "' ";
        }
        if (!"".equals(model.getPhone())) {
            sql += " and phone like '%" + model.getPhone() + "%' ";
        }
        if (!"".equals(model.getAddress())) {
            sql += " and address like '%" + model.getAddress() + "%' ";
        }
        if (model.getEnable()!=-1) {
            sql += " and enable = '" + model.getEnable() + "' ";
        }
        if (model.getStartAge()!=-1){
            sql += " and age >= '"+model.getStartAge()+"' ";
        }
        if (model.getEndAge()!=-1){
            sql += " and age <= '"+model.getEndAge()+"' ";
        }
        return sql;
    }
    /**
     * 根据id查询，用于修改用户信息
     * @param userModel
     * @return
     */
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id='"+userModel.getId()+"' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return UserModel.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .sex(query.getString("sex"))
                        .age(query.getInt("age"))
                        .phone(query.getString("phone"))
                        .address(query.getString("address"))
                        .email(query.getString("email"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .build();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public Integer count(UserModel model) {
        String sql = "select count(*) as count from `user` where true";
        sql += getWhereSql(model);
      return JDBC.count(sql);
    }


}
